clear all
set more off, perm
local mydir "`1'"
global Tables 	`mydir'/TS/Tables
global Work   	`mydir'/TS/DataWork
global Raw    	`mydir'/TS/DataRaw
global WRDS  	`mydir'/WRDS
/* ********************************************************************* */
* DEAL WITH EARNINGS DATA ONLY
* [#3A] load forecasts for dividend from IBES Unadjusted file
import sas using $WRDS/IBES/March2021/statsumu_epsus.sas7bdat, case(lower) clear
keep if (index(measure,"EPS")>0  & index(curcode,"USD")>0) 

replace ticker=trim(ticker)
drop if index(fpi,"A") | index(fpi,"B") | index(fpi,"C") | index(fpi,"D")

destring fpi, replace
keep if fpi<=9

keep ticker oftic cname cusip statpers fpi fpedats medest 
recast str cname
compress

reshape wide medest fpedats, i(ticker oftic cname cusip statpers) j(fpi)
order *, sequential
order statpers 
drop fpedats0
egen something=rownonmiss(medest1 medest2 medest3 medest4 medest5)
keep if something>0
drop something
* ****************************************************************
* one forecast is out of sequence and causes problems below for the tsset command
replace fpedats3=mdy(12,31,2000) if cusip=="35671D85" & mdy(1,14,1999)==statpers
replace fpedats4=mdy(12,31,2001) if cusip=="35671D85" & mdy(1,14,1999)==statpers
replace fpedats5=mdy(12,31,2002) if cusip=="35671D85" & mdy(1,14,1999)==statpers

*use quarterly data to build one-year forecast
replace medest1 =medest6+medest7+medest8+medest9 	if  medest1==. & (medest6+medest7+medest8+medest9!=medest2) & (fpedats2>fpedats9)
replace fpedats1=fpedats9 				if fpedats1==. & (fpedats2>fpedats9)
drop *6 *7 *8 *9 

gen medest6=.
gen medest7=.
gen fpedats6=.
gen fpedats7=.

order *, sequential
order statpers cname oftic ticker cusip 
generate augest1=medest1
forvalues x=2(1)7 {
	local j=`x'-1
	replace fpedats`x'=fpedats`j'+365  		if fpedats`x'==.
	generate augest`x'=medest`x'
	replace  augest`x'=augest`j'*(1+medest0/100) 	if augest`j'>0 & augest`x'==.
}
format fpedats6 fpedats7 %td

order *, sequential
order statpers cname oftic ticker cusip 

drop medest0 
* ****************************************
* add permcos -- use original links wherever possible so that i don't change the sample;  use new links were old one are missing
* "/mnt/brownresearch/ECON_LaPorta/rlaporta/research/Aggregate/ibes01_brown_2019.do" generates /srv/ibes/ibes01_brown_2019.dta 
merge m:1 ticker statpers using $Raw/ibes01_brown_2019, keepusing(permco permno ticker statpers)
* original file: /srv/ibes/ibes01_brown_2019
drop if _merge==2
drop _merge
* "/mnt/brownresearch/ECON_LaPorta/rlaporta/research/Aggregate/ibes01_brown_2019.do" generates /srv/ibes/ibes01_brown_2019.dta 
merge m:1 ticker statpers using $Raw/link_ibes_crsp2022, keepusing(permco permno ticker statpers) update
drop if _merge==2 | permco==.
drop _merge
* ****************************************
* restrict IBES sample to members of S&P
tempfile eps
save "`eps'", replace

import sas using $WRDS/CRSP/dsp500list.sas7bdat, clear case(lower)
joinby permno using "`eps'", unmatched(both)

keep if statpers>=start & statpers<=ending
drop start ending
count
* ****************************************
* there is only one observation for Hershey.  Drop it because i cannot use it for interpolation
drop if ticker=="HSY1" 
* ****************************************
* Need forecasts at horizons: 1-5 years;  need to interpolate data
* note: i do NOT need to adjust for splits (i am interpolating across fpis within a statpers)
reshape long medest augest fpedats, i(ticker oftic cname cusip statpers permco permno) j(fpi)

egen   id=group(permco permno statpers)
tsset id fpedats
tsfill
bysort id: ipolate medest fpedats, gen(feps)
bysort id: ipolate augest fpedats, gen(mfeps)

* pick forecasts at the desired horizons

* fill-in identifiers
sort id fpedats
foreach var of varlist ticker oftic cname cusip statpers permco permno  {
	quietly: replace `var'=`var'[_n-1] if missing(`var')==1 & id==id[_n-1]  
}
rename fpi fpi_ibes
* *********************************
generate fpi=(fpedats-statpers)/365
drop if (fpi<0) 
drop if fpi+mfeps==.

by id: egen max=max(fpi) 
by id: egen min=min(fpi) 

generate FPI=0 		if fpedats-statpers==92
replace  FPI=0 		if fpedats-statpers<=91 & fpi==min & min>(20/365) 
replace  FPI=fpi 	if fpi==1 | fpi==2 | fpi==3 | fpi==4 | fpi==5 | fpi==6 
replace  FPI=1 		if fpi==max & fpi>0.75 & fpi<1
keep if  FPI!=.

drop max
* may have multiple FPIs=0 
sort id FPI
by id FPI: egen max=max(fpi)
keep if fpi==max  

tabstat fpi, by(FPI) stat(min max mean count)

duplicates tag permco permno statpers fpedats, gen(mf)
tabulate mf

drop mf max min id medest fpi _merge
rename FPI fpi
* ****************************************
save $Work/cs01_eps, replace
* ****************************************